﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Abhiruchi_Phase1;

namespace PE_CE_PersonalINFO
{
    class PersonalInfoDAL
    {
        private static string connectionString = App.ConnectionString;
        //private static string connectionString = @"Data Source=.;Integrated Security=true;Initial catalog=Abhiruchi_Phase1_Local";
        //Insert Into DB\
        public static DataTable InsertDataintoDB(string Eid,string name, string lname, string gender, string date, string fname, string mstatus, string spousename, string Photo,string fphoto)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_PersonalInfo", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@Eid", Eid);
            mycom.Parameters.AddWithValue("@FirstName", name);
            mycom.Parameters.AddWithValue("@LastName", lname);
            mycom.Parameters.AddWithValue("@Gender", gender);
            mycom.Parameters.AddWithValue("@DateOfBirth", DateTime.Parse(date));
            mycom.Parameters.AddWithValue("@FatherName", fname);
            mycom.Parameters.AddWithValue("@MaritalStatus", mstatus);
            mycom.Parameters.AddWithValue("@SpouseName", spousename);
          //  mycom.Parameters.AddWithValue("@IsActive", isactive);
            mycom.Parameters.AddWithValue("@Photo", Photo);
            mycom.Parameters.AddWithValue("@FamilyPhoto", fphoto);
          //  mycom.Parameters.AddWithValue("@eType", etype);
          //  mycom.Parameters.AddWithValue("@flag", flag);
            mycom.Parameters.AddWithValue("@ptype", 5);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            return myDt;
        }

        public static DataTable GetPersonalInfoDB(string eid)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_PersonalInfo", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@ptype", 6);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            return myDt;
        }

        public static void UpdatePersonalInfotoDB(string eid, string name, string lname, string gender, string date, string fname, string mstatus, string spousename,string photo,string fphoto)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_PersonalInfo", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@FirstName", name);
            mycom.Parameters.AddWithValue("@LastName", lname);
            mycom.Parameters.AddWithValue("@Gender", gender);
            mycom.Parameters.AddWithValue("@DateOfBirth",DateTime.Parse( date));
            mycom.Parameters.AddWithValue("@FatherName", fname);
            mycom.Parameters.AddWithValue("@MaritalStatus", mstatus);
            mycom.Parameters.AddWithValue("@SpouseName", spousename);
          //  mycom.Parameters.AddWithValue("@IsActive", isactive);
            mycom.Parameters.AddWithValue("@Photo", photo);
            mycom.Parameters.AddWithValue("@FamilyPhoto", fphoto);
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@ptype", 2);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
        }

        //Insert PE_CE_PersonalInfo Documents
        public static void InsertDocstoDB(string eid,string DocName,string DocPath)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_DocMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@DocName", DocName);
            mycom.Parameters.AddWithValue("@DocPath", DocPath);
            mycom.Parameters.AddWithValue("@ptype",5);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
        }

        //Get PE_CE_PersonalDocumets Information
        public static DataTable Get_PE_CE_Documents(string eid)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_DocMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@ptype", 6);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            return myDt;
        }

        //Update PE_CE_PersonalInformationDocuments
        public static void UpdateDocumentstoDB(string eid, string DocName, string DocPath,int Docid)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_DocMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@DocName", DocName);
            mycom.Parameters.AddWithValue("@DocPath", DocPath);
            mycom.Parameters.AddWithValue("@Docid", Docid);
            mycom.Parameters.AddWithValue("@ptype", 2);            
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
        }

        //Delete documents from DB based ON docid
        public static void DeleteDocumentsfromDB(string eid,int Docid)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_HRM_Get_Set_PE_CE_DocMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);            
            mycom.Parameters.AddWithValue("@Docid", Docid);
            mycom.Parameters.AddWithValue("@ptype", 3);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
        }

        public static DataTable InsertdatatoEMPMaster(string eid,string ename,string etype,int isactive,int flag)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_Get_Set_HRM_EmployeeMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@EId", eid);
            mycom.Parameters.AddWithValue("@EName", ename);
            mycom.Parameters.AddWithValue("@EType", etype);            
            mycom.Parameters.AddWithValue("@IsActive", 1);
            mycom.Parameters.AddWithValue("@flag", flag);           
            mycom.Parameters.AddWithValue("@ptype", 3);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            return myDt;
        }

        public static void UpdateEMPMaster(string eid,string ename,string etype,int isactive)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_Get_Set_HRM_EmployeeMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@EId", eid);
            mycom.Parameters.AddWithValue("@EName", ename);
            mycom.Parameters.AddWithValue("@EType", etype);
            mycom.Parameters.AddWithValue("@IsActive", isactive);           
            mycom.Parameters.AddWithValue("@ptype", 4);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
        }

        public static DataTable GetEmployeeMasterInfo(string eid)
        {
            SqlConnection mycon = new SqlConnection(connectionString);
            SqlCommand mycom = new SqlCommand("SP_Get_Set_HRM_EmployeeMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@eid", eid);
            mycom.Parameters.AddWithValue("@ptype", 2);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            return myDt;
        }

    }

}
